﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using DimensionsHelper.Common.Contracts.Excel;
using DimensionsHelper.Common.Contracts.Services;
using DimensionsHelper.Common.Contracts.Sql;
using DimensionsHelper.Common.Sql;
using DimensionsHelper.Common.Utils;
using DimensionsHelper.Services.DataTable;
using DimensionsHelper.Services.Metadata;

namespace DimensionsHelper.Services.SQL;

/// <summary>
///     从Excel读取数据，完成SQL脚本相关的功能。
///     <list type="number">
///         <item>
///             构建UPDATE SQL脚本，输出到文本文件中。
///         </item>
///         <item>
///             记录修改的样本数量，并且可以输出结果表格到Excel文件中。
///         </item>
///     </list>
/// </summary>
public class UpdateSqlBuilder : IUpdateSqlBuilder
{
    private const string SqlFormat = "UPDATE VDATA SET {0} WHERE {1}";

    private const string QueryFormat = "{0} = {1}";
    private const string NumbersQueryFormat = "{0} - {1} = {}";
    private const string TextQueryFormat = "TRIM({0}) = '{1}'";
    private const string CategoryQueryFormat = "CONTAINSANY({0}, {1})";
    private const string CategoryUnionFormat = "UNION({0}, {1})";
    private const string CategoryDifferenceFormat = "DIFFERENCE({0}, {1})";

    private const string QueryAnd = " AND ";
    private const string QueryOr = " OR ";

    private const short OperateAppend = 0x1;
    private const short OperateDelete = 0x2;

    private const short FieldData = 0x4;
    private const short FieldWhere = 0x8;

    private const short FieldText = 0x10;
    private const short FieldCategorical = 0x20;

    private readonly List<string> _buffer = [];
    private readonly Dictionary<string, FieldInfo> _fieldKindCache = new();
    private readonly List<string> _keyFields = [];
    private readonly SqlMetadataProvider _provider =
        new(new MetadataDocument() /* 创建空白的元数据文档，此处不需要元数据检索功能 */, SqlFormat);
    private readonly SampleDataTable _sampleTable = new();


    public string Section => "SqlBuilder";

    /* 创建空白的元数据文档，此处不需要元数据检索功能 */

    public IFilePikerService? FilePikerService { get; set; }

    public IReportService? ReportService { get; set; }

    public MetadataCodeMap Settings { get; private set; } = new();

    public bool FillSampleDataTable { get; set; }

    public bool AutoMergeList { get; set; }

    /// <summary>
    ///     清空已读取的内容。
    /// </summary>
    public void Clear()
    {
        _provider.Clear();
        _buffer.Clear();
        _keyFields.Clear();
        _sampleTable.Clear();

        foreach (var col in _columns)
        {
            col.Reset();
        }

        _fieldKindCache.Clear();
    }


    public void Export(string filePath)
    {
        if (_provider.Count == 0)
        {
            ReportService?.Error("已读取的有效数据为空。");
            return;
        }

        FillBuffer();

        ReportService?.Log($"开始输出文件'{filePath}'。");

        var lines = _buffer.Count;
        double count = 0;

        using var writer = File.CreateText(filePath);
        foreach (var item in _buffer)
        {
            ReportService?.Progress(++count / lines * 100, string.Empty);
            writer.WriteLine(item);
        }
    }

    public void ExportDataTableToExcel(IExcelWorkbookOperator workbook)
    {
        if (_sampleTable.Rows == 0)
        {
            return;
        }

        try
        {
            var worksheet = workbook.GetWorksheet("SQL DATA");

            _sampleTable.SortRow();
            _sampleTable.PrependBase();
            _sampleTable.AppendSubtotal();
            _sampleTable.WriteToExcel(worksheet, 1, 2);

            worksheet.AutoFitEntireColumns();

            ReportService?.Directory(Path.GetDirectoryName(workbook.FileName) ?? "", "结果文件根路径");
            ReportService?.Directory(workbook.FileName, "结果文件路径");
        }
        catch (Exception e)
        {
            ReportService?.Error($"错误：{e.Message}。");
        }
    }


    public void ExportMetadata(string filePath)
    {
        // 不实现
    }


    public bool LoadSettings(string settingFileName)
    {
        try
        {
            var map = JsonHelper.ReadJson(settingFileName, ServiceJsonContext.Default.MetadataCodeMap);

            // 没有报错的情况下，视为读取成功，如果没有读取到配置，视为配置为空。
            if (map == null)
            {
                return true;
            }

            map.Refresh();
            Settings = map;

            // Validate map
            if (map.FieldMapping.Count != 0)
            {
                return true;
            }

            ReportService?.Error("'FieldMapping'表不能是空。");
            return false;

        }
        catch (Exception e)
        {
            ReportService?.Error($"JSON解析错误：{e.Message}");
            return false;
        }
    }

    public bool LoadSettings(MetadataCodeMap settings)
    {
        Settings.Refresh();
        Settings = settings;
        return true;
    }

    public bool LoadSettings(object settings)
    {
        if (settings is MetadataCodeMap codeMap)
        {
            return LoadSettings(codeMap);
        }

        return false;
    }

    public void ReadExcel(SqlBuilderInputFileInfo fileInfo)
    {
        ReadExcel(fileInfo, CancellationToken.None);
    }

    public void ReadExcel(IEnumerable<SqlBuilderInputFileInfo> fileInfos)
    {
        ReadExcel(fileInfos, CancellationToken.None);
    }

    public void ReadExcel(SqlBuilderInputFileInfo fileInfo, CancellationToken cancellationToken)
    {
        ReadExcel(fileInfo, true, cancellationToken);
    }

    public void ReadExcel(IEnumerable<SqlBuilderInputFileInfo> fileInfos, CancellationToken cancellationToken)
    {
        Clear();

        foreach (var file in fileInfos)
        {
            if (cancellationToken.IsCancellationRequested)
            {
                return;
            }

            ReadExcel(file, false, cancellationToken);
        }
    }

    public void SaveSetting(string settingFileName)
    {
    }

    private static bool IsChild(SqlMetadataProvider.Storage storage)
    {
        return storage.Parent != storage;
    }


    private static bool IsFirstChild(SqlMetadataProvider.Storage storage)
    {
        return IsChild(storage) && storage == storage.Parent.Begin;
    }


    private static bool IsLastChild(SqlMetadataProvider.Storage storage)
    {
        return IsChild(storage) && storage == storage.Parent.End;
    }

    // 格式化脚本时，对于QUERY部分的脚本，不同字段之间使用AND，相同字段的不同值使用OR
    private static void ToExpressionQuery(StringBuilder buf, SqlMetadataProvider.Storage storage)
    {
        if (buf.Length > 0)
        {
            buf.Append(IsChild(storage) && !IsFirstChild(storage) ? QueryOr : QueryAnd);
        }

        var format = string.Empty;
        var val = storage.FieldValue;

        if (storage.IsHierarchical && (storage.CustomFlag & FieldText) == 0)
        {
            if (!storage.IsCategorical)
            {
                format = NumbersQueryFormat;
                storage.IsCategorical = true;
            }

            storage.Combine((a, b) =>
            {
                if (a.CategoricalValue.Count == 0)
                {
                    a.CategoricalValue.Add(a.FieldValue);
                }

                a.CategoricalValue.Add(b.FieldValue);
                return true;
            });

            storage = storage.Begin ?? storage;
        }

        if (string.IsNullOrEmpty(format))
        {
            if (storage.IsCategorical)
            {
                format = CategoryQueryFormat;
            }
            else if (storage.HasCustomFlag(FieldText))
            {
                format = TextQueryFormat;
                val = val.Trim();
            }
            else
            {
                format = QueryFormat;
            }
        }

        if (IsFirstChild(storage))
        {
            buf.Append('(');
        }

        buf.AppendFormat(format, storage.FieldName, val);

        if (IsLastChild(storage))
        {
            buf.Append(')');
            return;
        }

        if (!IsChild(storage))
        {
            return;
        }

        ToExpressionQuery(buf, storage.NextStorage);
    }


    private void ToExpressionUpdate(List<string> buf, SqlMetadataProvider.Storage storage)
    {
        if (storage.IsCategorical)
        {
            if (Settings.CategoricalSqlType == CategoricalSqlUpdateType.PlusSubtract)
            {
                var bufItem = new StringBuilder();
                bufItem.Append(storage.HasCustomFlag(OperateAppend) ? '+' : '-');
                buf.Add(bufItem.Append(' ').Append(storage.FieldValue).ToString());
            }
            else
            {
                buf.Add(string.Format(
                    storage.HasCustomFlag(OperateAppend) ? CategoryUnionFormat : CategoryDifferenceFormat,
                    storage.FieldName, storage.FieldValue));
            }
        }
        else
        {
            var val = storage.FieldValue;

            if (storage.HasCustomFlag(FieldText) ||
                (!storage.IsCategorical && !double.TryParse(val, out _)))
            {
                val = $"'{val}'";
            }

            buf.Add(string.Format(QueryFormat, storage.FieldName, val));
        }

        if (!IsChild(storage) || IsLastChild(storage))
        {
            return;
        }

        ToExpressionUpdate(buf, storage.NextStorage);
    }


    /// <summary>
    ///     以<paramref name="a" />为基准，比较<paramref name="b" />是否和<paramref name="a" />相同。
    /// </summary>
    private static bool IsStorageRepetitive(SqlMetadataProvider.Storage a, SqlMetadataProvider.Storage b)
    {
        if (a.IsCategorical)
        {
            return a.CategoricalValue.ContainsAll(b.CategoricalValue) && a.CustomFlag == b.CustomFlag;
        }

        return a.FieldValue.Equals(b.FieldValue) && a.CustomFlag == b.CustomFlag;
    }


    /// <summary>
    ///     移除多层存储中的重复值，由于链表不会很长，此处采用简单的遍历比较每两个值。
    /// </summary>
    private static SqlMetadataProvider.Storage DropDuplicatedStorage(SqlMetadataProvider.Storage storage)
    {
        storage.Combine(IsStorageRepetitive);
        return storage.Begin ?? storage;
    }


    private SqlMetadataProvider.Storage MergeStorageCategorical(SqlMetadataProvider.Storage storage)
    {
        if (!storage.IsHierarchical)
        {
            return storage.Begin ?? storage;
        }

        if (!Settings.MergeUpdateRecord)
        {
            return DropDuplicatedStorage(storage);
        }

        // 按分类值的添加顺序遍历分类值，为了避免因顺序不同产生的错误，只合并CustomFlag相同的连续对象。
        storage.Combine((a, b) =>
        {
            if (IsStorageRepetitive(a, b))
            {
                return true;
            }

            var f = a.CustomFlag & b.CustomFlag;

            if (b.PreviousStorage == a && (f & (OperateAppend | OperateDelete)) != 0)
            {
                a.CategoricalValue.Add(b.CategoricalValue);
                return true;
            }

            return false;
        });

        return storage.Begin ?? storage;
    }


    private string GetSingleUpdateField(string fieldName, string buf)
    {
        if (Settings.CategoricalSqlType == CategoricalSqlUpdateType.PlusSubtract)
        {
            return fieldName + " = " + fieldName + " " + buf;
        }

        return fieldName + " = " + buf;
    }


    private void MergeUpdateFields(StringBuilder fieldBuffer, List<FieldBuffer> buffers)
    {
        foreach (var b in buffers)
        {
            if (fieldBuffer.Length > 0)
            {
                fieldBuffer.Append(", ");
            }

            fieldBuffer.Append(b.FieldName).Append(" = ");

            if (Settings.CategoricalSqlType == CategoricalSqlUpdateType.PlusSubtract)
            {
                fieldBuffer.Append(b.FieldName).Append(' ').Append(string.Join(" ", b.Buffer));
            }
            else
            {
                fieldBuffer.Append(string.Join(", " + b.FieldName + " = ", b.Buffer));
            }
        }
    }


    private void ToExpression(SqlMetadataProvider.Record record)
    {
        var query = new StringBuilder();
        var buf = new List<FieldBuffer>();

        foreach (var storage in record.Values)
        {
            var s = MergeStorageCategorical(storage);
            if ((s.CustomFlag & FieldWhere) != 0)
            {
                ToExpressionQuery(query, s);
            }
            else
            {
                var fieldBuf = new List<string>();
                ToExpressionUpdate(fieldBuf, s);
                buf.Add(new FieldBuffer(storage.FieldName, fieldBuf));
            }
        }

        if (query.Length == 0 || buf.Count == 0)
        {
            ReportService?.Warn($"格式化脚本：记录 {record.KeyValue} 错误，UPDATE语句或QUERY语句过少。");
            return;
        }

        var queryExpr = query.ToString();

        if (!Settings.MergeUpdateRecord)
        {
            foreach (var b in buf)
            {
                foreach (var item in b.Buffer)
                {
                    _buffer.Add(string.Format(SqlFormat, GetSingleUpdateField(b.FieldName, item), queryExpr));
                }
            }
        }
        else
        {
            var updateBuffer = new StringBuilder();
            MergeUpdateFields(updateBuffer, buf);
            _buffer.Add(string.Format(SqlFormat, updateBuffer, queryExpr));
        }
    }


    private void FillBuffer()
    {
        var i = 0;
        ReportService?.Log("开始填充缓冲区 ...");
        foreach (var record in _provider)
        {
            ToExpression(record);
            ReportService?.Progress((double)++i / _provider.Count * 100, "填充进度");
        }

        ReportService?.Log("填充完成。");
    }

    private class UpdateColumn
    {
        private readonly UpdateColumnKinds _kind;
        private int _col;
        private List<int>? _columns;

        internal UpdateColumn(UpdateColumnKinds kind)
        {
            _kind = kind;
            _col = 0;
        }

        public int Count
        {
            get
            {
                if (AllowMultiple)
                {
                    return _columns?.Count ?? 0;
                }

                return _col > 0 ? 1 : 0;
            }
        }

        private bool AllowMultiple => _kind != UpdateColumnKinds.Id;

        public void Reset()
        {
            _col = 0;
            _columns = null;
        }

        public void SetColumn(int column)
        {
            if (AllowMultiple)
            {
                _columns ??= [];

                if (!_columns.Contains(column))
                {
                    _columns.Add(column);
                }
            }
            else
            {
                _col = column;
            }
        }

        public int GetColumn()
        {
            if (AllowMultiple)
            {
                return _columns is { Count: > 0 } ? _columns[_columns.Count - 1] : 0;
            }

            return _col;
        }

        public IEnumerable<int> GetColumns()
        {
            if (AllowMultiple && _columns != null)
            {
                return _columns;
            }

            if (!AllowMultiple && _col > 0)
            {
                return [_col];
            }

            return Array.Empty<int>();
        }


        public bool HasColumn(int column)
        {
            if (AllowMultiple && _columns != null)
            {
                return _columns.Contains(column);
            }

            if (!AllowMultiple && _col > 0)
            {
                return _col == column;
            }

            return false;
        }
    }

    private class FieldInfo
    {
        public string? Label { get; set; }

        public short Flags { get; set; }
    }


    private class FieldBuffer(string fileName, List<string> buffer)
    {
        public string FieldName { get; } = fileName;
        public List<string> Buffer { get; } = buffer;
    }


    #region Read Excel

    private class FieldValueColumnPair
    {
        public int FieldColumn { get; set; }
        public int ValueColumn { get; set; }
        public int DeleteColumn { get; set; }
        public int ExtraColumn { get; set; }
    }

    // id列不允许多列。
    private readonly UpdateColumn[] _columns =
    [
        new(UpdateColumnKinds.Id),
        new(UpdateColumnKinds.Code),
        new(UpdateColumnKinds.Variable),
        new(UpdateColumnKinds.Delete),
        new(UpdateColumnKinds.Extra)
    ];

    // 字段列后如果紧接着数据列，将这两列绑定。如果下一列还是数据列，则需要清空绑定。
    private bool _canBindFieldValueColumns = true;
    private bool _canAppendExtraFields = true;
    private readonly List<FieldValueColumnPair> _fieldValueColumnPairs = new List<FieldValueColumnPair>();
    private readonly List<FieldValueColumnPair> _extraValueColumnPairs = new List<FieldValueColumnPair>();

    private void FillColumnFromPair(List<FieldValueColumnPair> pairs)
    {
        foreach (var pair in pairs)
        {
            _columns[(int)UpdateColumnKinds.Variable].SetColumn(pair.FieldColumn);

            if (pair.ValueColumn > 0)
            {
                _columns[(int)UpdateColumnKinds.Code].SetColumn(pair.ValueColumn);
            }

            if (pair.DeleteColumn > 0)
            {
                _columns[(int)UpdateColumnKinds.Delete].SetColumn(pair.DeleteColumn);
            }

            if (pair.ExtraColumn > 0)
            {
                _columns[(int)UpdateColumnKinds.Extra].SetColumn(pair.ExtraColumn);
            }
        }
    }

    private void PrepareColumnPair(UpdateColumnKinds kind, int col)
    {
        var pair = new FieldValueColumnPair
        {
            FieldColumn = col - 1
        };

        switch (kind)
        {
            case UpdateColumnKinds.Code:
                pair.ValueColumn = col;
                break;
            case UpdateColumnKinds.Delete:
                pair.DeleteColumn = col;
                break;
            default:
                pair.ExtraColumn = col;
                break;
        }

        if (kind == UpdateColumnKinds.Extra)
        {
            _extraValueColumnPairs.Add(pair);
        }
        else
        {
            _fieldValueColumnPairs.Add(pair);
        }
    }

    private bool PrepareColumns(string[] headers, string sheetName)
    {
        foreach (var col in _columns)
        {
            col.Reset();
        }

        _fieldValueColumnPairs.Clear();
        _extraValueColumnPairs.Clear();

        _canBindFieldValueColumns = true;
        _canAppendExtraFields = true;

        var lastKind = UpdateColumnKinds.Extra;

        for (var i = 0; i < headers.Length; ++i)
        {
            var col = i + 1;
            var str = headers[i];
            if (Settings.TryGetUpdateColumnKind(str, out var kind))
            {
                if ((kind == UpdateColumnKinds.Code ||
                     kind == UpdateColumnKinds.Delete ||
                     kind == UpdateColumnKinds.Extra) &&
                    _canBindFieldValueColumns)
                {
                    // +----------+-------------------+
                    // | variable | code/delete/extra |
                    // +----------+-------------------+
                    // 变量列和Extra列相邻时，创建新的列分组
                    if (lastKind == UpdateColumnKinds.Variable)
                    {
                        PrepareColumnPair(kind, col);
                    }
                    // +--------+----------+
                    // |  code  |  delete  |
                    // +--------+----------+
                    // 码号列和删除列相邻时，如果已经添加了分组并且code列和上一个分组的列号相同，更新该分组的Delete列。
                    else if (lastKind == UpdateColumnKinds.Code && kind == UpdateColumnKinds.Delete &&
                             _fieldValueColumnPairs.Count > 0 &&
                             _fieldValueColumnPairs[_fieldValueColumnPairs.Count - 1].ValueColumn + 1 == col)
                    {
                        _fieldValueColumnPairs[_fieldValueColumnPairs.Count - 1].DeleteColumn = col;
                    }
                    else
                    {
                        if (_fieldValueColumnPairs.Count > 0)
                        {
                            FillColumnFromPair(_fieldValueColumnPairs);
                            _fieldValueColumnPairs.Clear();
                        }

                        if (_extraValueColumnPairs.Count > 0)
                        {
                            FillColumnFromPair(_extraValueColumnPairs);
                            _extraValueColumnPairs.Clear();
                        }

                        _columns[(int)kind].SetColumn(col);
                        _canBindFieldValueColumns = false;
                    }
                }
                else
                {
                    _columns[(int)kind].SetColumn(col);
                }

                lastKind = kind;
            }
        }

        if (_columns[(int)UpdateColumnKinds.Id].Count == 0)
        {
            ReportService?.Warn($"表格 {sheetName} 查询到的ID列为空，已跳过。");
            return false;
        }

        if ((_canBindFieldValueColumns && _fieldValueColumnPairs.Count == 0) ||
            (!_canBindFieldValueColumns &&
             (_columns[(int)UpdateColumnKinds.Variable].Count == 0 ||
              (_columns[(int)UpdateColumnKinds.Code].Count == 0 &&
               _columns[(int)UpdateColumnKinds.Delete].Count == 0))))
        {
            ReportService?.Warn($"表格 {sheetName} 查询到的字段列为空，已跳过。");
            return false;
        }

        return true;
    }


    private static string GetSheetText(IExcelWorksheetOperator sheet, int row, int col)
    {
        if (row <= 0 || col <= 0)
        {
            return string.Empty;
        }

        return sheet.GetCellText(row, col);
    }


    private bool ReadRecord(IExcelWorksheetOperator sheet, SqlMetadataProvider.Record record,
        FieldValueColumnPair pair, int row)
    {
        if (pair.FieldColumn <= 0 || pair is { ValueColumn: <= 0, DeleteColumn: <= 0 })
        {
            return false;
        }

        var fieldName = sheet.GetCellText(row, pair.FieldColumn);

        if (string.IsNullOrEmpty(fieldName) || Settings.IsFieldIgnored(fieldName))
        {
            return false;
        }

        var value = GetSheetText(sheet, row, pair.ValueColumn);
        var deleted = GetSheetText(sheet, row, pair.DeleteColumn);

        if (string.IsNullOrEmpty(value) && string.IsNullOrEmpty(deleted))
        {
            return false;
        }

        if (!string.IsNullOrEmpty(value))
        {
            CheckAndUpdateStorageWithFlags(record, fieldName, value, FieldData | OperateAppend);
        }

        if (!string.IsNullOrEmpty(deleted))
        {
            CheckAndUpdateStorageWithFlags(record, fieldName, deleted, FieldData | OperateDelete);
        }

        return true;
    }


    /// <summary>
    ///     读取类型是<see cref="UpdateColumnKinds.Extra" />的列单元格内容，并将结果填写进<paramref name="extraFields" />和
    ///     <paramref name="extraValues" />中。
    ///     填写过程中，不会检查这两个列表中的值的重复性，一直会同步添加内容。因此，此方法不会改变两个列表的长度差。
    /// </summary>
    private void ReadExtra(IExcelWorksheetOperator sheet, int row, string[] heads, List<string> extraFields,
        List<string> extraValues)
    {
        if (_extraValueColumnPairs.Count > 0)
        {
            foreach (var pair in _extraValueColumnPairs)
            {
                if (pair.FieldColumn <= 0 || pair.ExtraColumn <= 0)
                {
                    continue;
                }

                var fieldName = sheet.GetCellText(row, pair.FieldColumn);
                var val = sheet.GetCellText(row, pair.ExtraColumn);

                if (string.IsNullOrEmpty(fieldName) || string.IsNullOrEmpty(val))
                {
                    continue;
                }

                extraFields.Add(fieldName);
                extraValues.Add(val);
            }
        }
        else
        {
            var cols = _columns[(int)UpdateColumnKinds.Extra];
            foreach (var col in cols.GetColumns())
            {
                var head = heads[col - 1];
                var val = sheet.GetCellText(row, col);

                if (string.IsNullOrEmpty(head) || string.IsNullOrEmpty(val))
                {
                    continue;
                }

                extraFields.Add(head);
                extraValues.Add(val);
            }
        }
    }


    /// <summary>
    ///     只检查存储数据的类型，不检查具体数值，此方法可能会重复添加相同的值。由于判定值是否是重复值，需要伴有Flag信息，此处不对
    ///     可能出现的重复值进行处理。
    /// </summary>
    private SqlMetadataProvider.Storage TryUpdateStorage(SqlMetadataProvider.Storage storage, string value)
    {
        var final = storage;

        try
        {
            if (storage.IsHierarchical)
            {
                final = storage.AddSubStorage(value);
            }
            else
            {
                if (storage is { IsCategorical: true, IsEmpty: false })
                {
                    storage.ConvertToHierarchical();
                    final = storage.AddSubStorage(value);
                }
                else
                {
                    storage.FieldValue = value;
                }
            }
        }
        catch (Exception e)
        {
            ReportService?.Warn($"更新字段值错误: {e.Message}");
        }

        return final;
    }


    private FieldInfo CheckFieldKind(string fieldName)
    {
        var lower = fieldName.ToLower();

        if (_fieldKindCache.TryGetValue(lower, out var info))
        {
            return info;
        }

        info = new FieldInfo();

        if (Settings.TryGetElement(fieldName, out var element))
        {
            short flags = 0;

            if (element.IsText)
            {
                flags = FieldText;
            }
            else if (element is { IsLong: false, IsDouble: false })
            {
                flags = FieldCategorical;
            }

            info.Flags = flags;
            info.Label = element.Label;
        }
        else
        {
            info.Flags = FieldCategorical;
        }

        _fieldKindCache.Add(lower, info);
        return info;
    }


    private SqlMetadataProvider.Storage CheckAndUpdateStorage(SqlMetadataProvider.Record record, string fieldName,
        string fieldValue)
    {
        var hasFieldLabel = false;
        var info = CheckFieldKind(fieldName);
        var isCategorical = (info.Flags & FieldCategorical) != 0;
        var isText = (info.Flags & FieldText) != 0;
        if (info.Label != null)
        {
            fieldValue = info.Label + fieldValue;
            hasFieldLabel = true;
        }

        if (!hasFieldLabel && isCategorical)
        {
            fieldValue = Settings.GetCode(fieldValue);
        }

        var storage = record.GetStorage(Settings.GetField(fieldName, out _));
        storage.IsCategorical = isCategorical;
        storage = TryUpdateStorage(storage, fieldValue);

        if (isText)
        {
            storage.CustomFlag |= FieldText;
        }

        return storage;
    }


    private void UpdateSampleDataTable(string keyValue, string fieldName, string fieldValue, short flags)
    {
        fieldName = Settings.GetField(fieldName, out _);

        if ((flags & OperateAppend) != 0)
        {
            _sampleTable[fieldValue][fieldName].IncreaseSample(keyValue);
        }
        else if ((flags & OperateDelete) != 0)
        {
            _sampleTable[fieldValue][fieldName].DecreaseSample(keyValue);
        }
    }


    private void CheckAndUpdateStorageWithFlags(SqlMetadataProvider.Record record, string fieldName,
        string fieldValue,
        short flags)
    {
        var storage = CheckAndUpdateStorage(record, fieldName, fieldValue);
        storage.CustomFlag |= flags;
        UpdateSampleDataTable(record.KeyValue, fieldName, fieldValue, flags);
    }


    private void FillRecordExtra(SqlMetadataProvider.Record record, List<string> fields, List<string> values)
    {
        if (!_canAppendExtraFields || fields.Count == 0 || values.Count == 0)
        {
            return;
        }

        for (var i = 0; i < values.Count; i++)
        {
            if (Settings.IsFieldIgnored(fields[i]))
            {
                continue;
            }

            // 检查额外字段是否已存在，如果已存在，检查新添加的值是否存在，如果存在，需要加入到存储值中。
            // Extra和Code/Delete是不同的，只有一种Flag，同一个字段的不同值之间是"或"的关系，
            // 因此，在更新数据时
            // 1. 对于Categorical分类值，应当将新值加入到已有分类值中
            // 2. 对于字符串或者数值类型值，应当先检查更新值是否和已有值相同，如果不同，再加入到下级存储。
            if (record.HasStorage(fields[i]))
            {
                var storage = record.GetStorage(fields[i]);
                if (storage.IsCategorical)
                {
                    storage.JoinCode(values[i]);
                }
                // 如果类型不是Categorical，需要加入下级存储。
                else if (storage.FieldValue != values[i])
                {
                    storage.ConvertToHierarchical();
                    storage.AddSubStorage(values[i]);
                }
            }
            else
            {
                CheckAndUpdateStorageWithFlags(record, fields[i], values[i], FieldWhere);
            }
        }
    }


    private string GetRecordKey(string keyValue, List<string> fieldNames, List<string> fieldValues)
    {
        var vals = new string[_keyFields.Count + 1];
        vals[0] = keyValue;
        var size = fieldNames.Count;

        for (var i = 0; i < size; i++)
        {
            if (i >= fieldValues.Count)
            {
                break;
            }

            var index = _keyFields.FindIndex(e => e.Equals(fieldNames[i], StringComparison.OrdinalIgnoreCase));

            if (index < 0)
            {
                _keyFields.Add(fieldNames[i]);
                Array.Resize(ref vals, vals.Length + 1);
                vals[vals.Length - 1] = fieldValues[i];
            }
            else
            {
                if (!string.IsNullOrEmpty(vals[index + 1]))
                {
                    vals[index + 1] += ';';
                }

                vals[index + 1] += fieldValues[i];
            }
        }

        return string.Join(";", vals);
    }


    /// <summary>
    ///     读取单个存储条目，其中，类型是<see cref="UpdateColumnKinds.Extra" />的列的内容参与条目关键字的区分，
    ///     因此，需要提前读取，并且，无论字段名是否在<see cref="Settings" />的忽略字段名中，都应该参与构建条目关键字。
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="row"></param>
    /// <param name="heads"></param>
    private void ReadRecord(IExcelWorksheetOperator sheet, int row, string[] heads)
    {
        var id = sheet.GetCellText(row, _columns[(int)UpdateColumnKinds.Id].GetColumn());

        if (string.IsNullOrEmpty(id))
        {
            return;
        }

        // extra 条件列影响条目ID，优先检索
        var extraFields = new List<string>();
        var extraValues = new List<string>();
        ReadExtra(sheet, row, heads, extraFields, extraValues);

        var record = _provider.GetRecord(GetRecordKey(id, extraFields, extraValues));

        CheckAndUpdateStorageWithFlags(record, Settings.KeyField, id, FieldWhere);
        FillRecordExtra(record, extraFields, extraValues);

        var successCount = 0;
        if (_fieldValueColumnPairs.Count > 0)
        {
            successCount += _fieldValueColumnPairs.Count(pair => ReadRecord(sheet, record, pair, row));
        }
        else
        {
            var fieldName = sheet.GetCellText(row, _columns[(int)UpdateColumnKinds.Variable].GetColumn());

            if (!string.IsNullOrEmpty(fieldName) && !Settings.IsFieldIgnored(fieldName))
            {
                for (var i = 0; i < heads.Length; i++)
                {
                    var col = i + 1;
                    var val = sheet.GetCellText(row, col);

                    if (string.IsNullOrEmpty(val))
                    {
                        continue;
                    }

                    short flag = 0;
                    if (_columns[(int)UpdateColumnKinds.Code].HasColumn(col))
                    {
                        flag = FieldData | OperateAppend;
                    }
                    else if (_columns[(int)UpdateColumnKinds.Delete].HasColumn(col))
                    {
                        flag = FieldData | OperateDelete;
                    }

                    if (flag > 0)
                    {
                        CheckAndUpdateStorageWithFlags(record, fieldName, val, flag);
                        successCount++;
                    }
                }
            }
        }

        if (successCount != 0)
        {
            return;
        }

        _provider.Remove(record.KeyValue);
        ReportService?.Error($"工作表 {sheet.Name} 行 {row} 的有效数据为空。");
    }


    private string[] ReadHeader(IExcelWorksheetOperator sheet)
    {
        if (sheet.EndColumn == 0 || sheet.EndRow <= Settings.StartRow - 1)
        {
            return [];
        }

        var headers = new string[sheet.EndColumn];

        for (var i = 0; i < sheet.EndColumn; i++)
        {
            headers[i] = sheet.GetCellText(Settings.StartRow - 1, i + 1);
        }

        return headers;
    }


    private void ReadExcel(IExcelWorksheetOperator sheet, CancellationToken token)
    {
        ReportService?.Log($"开始读取工作表：{sheet.Name}。");

        var headers = ReadHeader(sheet);

        if (headers.Length == 0 || !PrepareColumns(headers, sheet.Name))
        {
            return;
        }

        for (var i = Settings.StartRow; i <= sheet.EndRow; i++)
        {
            if (token.IsCancellationRequested)
            {
                return;
            }

            ReadRecord(sheet, i, headers);
        }

        ReportService?.Log($"工作表'{sheet.Name}'读取已完成。");
    }


    private void ReadExcel(SqlBuilderInputFileInfo fileInfo, bool clear, CancellationToken token)
    {
        if (clear)
        {
            Clear();
        }

        try
        {
            if (!fileInfo.Operator.IsInitialized)
            {
                fileInfo.Operator.Open(fileInfo.FileName);
            }

            foreach (var sheet in fileInfo.Operator.Worksheets)
            {
                if (token.IsCancellationRequested)
                {
                    return;
                }

                ReadExcel(sheet, token);
            }

            fileInfo.Operator.Close();
        }
        catch (Exception e)
        {
            ReportService?.Error(e.Message);
        }
    }

    #endregion
}